package net.huashitong.Indicators.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.Indicators.dto.AB01DTO;
import net.huashitong.Indicators.dto.StudentDTO;
import net.huashitong.Indicators.entity.AB02;
import net.huashitong.Indicators.interfacedao.SequenceDao;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.stereotype.Repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

@Repository
public class AB02Dao extends StringDao<AB02> implements SequenceDao {

    @Override
    public String getSequence(String seqName) {
        String sql  = "select "+seqName+".nextval from dual ";
        SQLQuery query = getSession().createSQLQuery(sql);
        List list = query.list();
        list.get(0).toString();
        return list.get(0).toString();
    }

    public List<AB01DTO> getBettingRank(String name, String courseID)
    {
        return null;
    }


    public AB02 getBetRecode(String aab001,String bab001,String aab998)
    {
        GenericQuery query = createQuery("obj");
        if(StringUtils.isNotBlank(aab001))
        {
            query.append(" and obj.AAB001=:aab001").setParam("aab001",aab001);
        }

        query.append(" and obj.BAB001=:bab001").setParam("bab001",bab001);
        if(StringUtils.isNotBlank(aab998))
        {
            query.append(" and obj.AAB998=:AAB998").setParam("AAB998",aab998);
        }

        return query.singleResult();
    }

    /**
     * 获取已经选择的课程
     * @param dto
     * @return
     */
    public List<StudentDTO> getOwnerCourse(StudentDTO dto)
    {
        StringBuffer sql = new StringBuffer();
        sql.append(" select t2.sys_id \"sysId\",t3.bab008,t3.bab007,t3.bab002,t3.bab003,t3.bab004,t3.bab001,t3.bab005,t2.aab996,t1.aab001,t1.aab002,t2.aab998,t1.aab999,t2.aaa001 ");
        sql.append(" from ab01 t1 ,ab02 t2, ba01 t3 ");
        sql.append(" where 1=1 ");
        sql.append(" and t1.aab001=t2.aab001 ");
        sql.append(" and t2.bab001=t3.bab001 ");
        if(StringUtils.isNotBlank(dto.getAAA001()))
        {
            sql.append(" and t1.aaa001='"+dto.getAAA001()+"'");
        }

        if(StringUtils.isNotBlank(dto.getAAB001()))
        {
            sql.append("and t1.aab001='"+dto.getAAB001()+"'");
        }
        if(StringUtils.isNotBlank(dto.getAAB998()))
        {
            sql.append(" and t1.aab998='"+dto.getAAB998()+"'");
        }
        if(StringUtils.isNotBlank(dto.getSysId()))
        {
            sql.append(" and t2.sys_id ='"+dto.getSysId()+"'");
        }
        if (StringUtils.isNotBlank(dto.getBAB001())){
            sql.append(" and t3.bab001 = '"+dto.getBAB001()+"'");
        }
        if (StringUtils.isNotBlank(dto.getBAB002()))
            sql.append(" and t3.bab002 ='"+dto.getBAB002()+"'");
        if (StringUtils.isNotBlank(dto.getBAB003()))
            sql.append(" and t3.bab003 ='"+dto.getBAB003()+"'");
        if (StringUtils.isNotBlank(dto.getBAB004()))
            sql.append(" and t3.bab004 ='"+dto.getBAB004()+"'");
        if (StringUtils.isNotBlank(dto.getBAB005()))
            sql.append(" and t3.bab005 ='"+dto.getBAB005()+"'");
        if (StringUtils.isNotBlank(dto.getBAB006()))
            sql.append(" and t3.bab006 ='"+dto.getBAB006()+"'");
        if (StringUtils.isNotBlank(dto.getBAB007())){
            sql.append(" and t3.bab007 ='"+dto.getBAB007()+"'");
        }
        if (StringUtils.isNotBlank(dto.getBAB008()))
            sql.append(" and t3.bab008 ='"+dto.getBAB008()+"'");
        if (StringUtils.isNotBlank(dto.getBAB009()))
            sql.append(" and t3.bab009 ='"+dto.getBAB009()+"'");


        String hql = sql.toString();

        hql = new StringBuffer(" select count(1) ").append(" from ( ").append(hql).append(" ) temp ").toString();
        Query getcount = getSession().createSQLQuery(hql);
        int count =((Number) getcount.uniqueResult()).intValue();
        int resultSize = count;
        int size =dto.getPaging().getPageSize();
        int page =dto.getPaging().getCurrentPage();
        int MaxPage = resultSize/size;
        if(!(resultSize%size==0) ){
            MaxPage = (resultSize/size)+1;
        }if(page>MaxPage)
        {
            dto.getPaging().setCurrentPage(MaxPage);
        }

        SQLQuery query  = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(StudentDTO.class));
        dto.getPaging().setRecords(resultSize);
        query.setFirstResult(dto.getPaging().getStartRow());
        query.setMaxResults(dto.getPaging().getPageSize());
        query.setMaxResults(size);

        List<StudentDTO> list =  (List<StudentDTO>) query.list();
        return  list;
    }

    //获取排名
    public List<StudentDTO> getCourseRank2(StudentDTO dto)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("  select  aab101, aab001,bab001,aab996,aab998 from ( ");
        sql.append("  select rank() over( order by ( a.aab996+0) desc ) aab101, a.aab001,a.bab001,a.aab996,a.aab998   from ab02 a ");
        sql.append("  where 1=1 ");
        sql.append("  and a.bab001 = '"+dto.getBAB001()+"'");
        sql.append("  and a.aaa001 ='0' ");
        sql.append(" ) t  ");
        sql.append(" where 1=1 and t.aab998 ='"+dto.getAAB998()+"' ");

        String hql = sql.toString();

        hql = new StringBuffer(" select count(1) ").append(" from ( ").append(hql).append(" ) temp ").toString();
        Query getcount = getSession().createSQLQuery(hql);
        int count =((Number) getcount.uniqueResult()).intValue();
        int resultSize = count;
        int size =28;
        int page =dto.getPaging().getCurrentPage();
        int MaxPage = resultSize/size;
        if(!(resultSize%size==0) ){
            MaxPage = (resultSize/size)+1;
        }if(page>MaxPage)
        {
            dto.getPaging().setCurrentPage(MaxPage);
        }

        SQLQuery query  = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(StudentDTO.class));
        dto.getPaging().setRecords(resultSize);
        query.setFirstResult(dto.getPaging().getStartRow());
        query.setMaxResults(size);
        List<StudentDTO> list =  (List<StudentDTO>) query.list();
        return  list;
    }

    //获取排名
    public List<StudentDTO> getCourseRank(StudentDTO dto)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("  select rank() over( order by ( a.aab996+0) desc ) aab101, a.aab001,a.bab001,a.aab996,a.aab998   from ab02 a ");
        sql.append("  where 1=1 ");
        sql.append("  and a.bab001 = '"+dto.getBAB001()+"'");
        if(StringUtils.isNotBlank(dto.getAAA001()))
        {
            sql.append("  and a.aaa001 ='"+dto.getAAA001()+"'");
        }

        String hql = sql.toString();

        hql = new StringBuffer(" select count(1) ").append(" from ( ").append(hql).append(" ) temp ").toString();
        Query getcount = getSession().createSQLQuery(hql);
        int count =((Number) getcount.uniqueResult()).intValue();
        int resultSize = count;
        int size =28;
        int page =dto.getPaging().getCurrentPage();
        int MaxPage = resultSize/size;
        if(!(resultSize%size==0) ){
            MaxPage = (resultSize/size)+1;
        }if(page>MaxPage)
    {
        dto.getPaging().setCurrentPage(MaxPage);
    }

        SQLQuery query  = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(StudentDTO.class));
        dto.getPaging().setRecords(resultSize);
        query.setFirstResult(dto.getPaging().getStartRow());
        query.setMaxResults(size);
        List<StudentDTO> list =  (List<StudentDTO>) query.list();
        return  list;
    }


    /**
     *
     * @param bab001 课程ID
     * @param bab003  课程名额
     * @param bab205
     */
    public void batchOverCourse(String bab001,String bab003,String bab205)
    {

       // ResultSet resultSet = null;
        try {
            Connection con = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
            String call = "{ call over_betting(?,?)}";
            CallableStatement cs = con.prepareCall(call);
            cs.setString(1,bab001);
            cs.setString(2,bab003);
            cs.execute();

        }catch (SQLException e)
        {

        }



    }

    //删除所有记录
    public void deleteAllAb02()
    {
        GenericQuery  delete = create("delete from AB02 t ");
        delete.executeUpdate();

    }


}
